<?php

include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/header.inc');
include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/comp.inc');
include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/fcs.php');
?>


<?php


if(isset($_GET['task']))
{
		if(!$link=mysql_connect("localhost",$user,$pass))
			die("Error in Database Connection");

		if(!mysql_select_db("bs"))
			echo("Error in Database Selection");

		if($_GET['task']=="displayform")
		{
				$fromdate="01-".date("m-Y");
				$todate="31-".date("m-Y");
				$arr=explode("-",$todate);
			
				while(!checkdate($arr[1],$arr[0],$arr[2]))
				{
						$arr[0]=$arr[0]-1;
				}
				$todate=$arr[0]."-".$arr[1]."-".$arr[2];
			
?>

				| <a href="display.php">Display </a> | <a href=pbook_display.php?task=displayform>Purchase Book</a></div>
				<h3>Select Date Range</h3>
				<table cellpadding="3" class="list">
				<form name="displayform" action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
				<tr><td><label>From </label></td><td> <input type="text" name="fromdate" size="12" value="<?php echo $fromdate; ?>" /></td></tr>
				<tr><td><label>To </label></td><td> <input type="text" name="todate" size="12" value="<?php echo $todate; ?>" /></td></tr>
				<tr><td><label>Searching Bill</label></td><td><input type="text" name="search" /></td></tr>
				<tr><td><label>Mode</label></td><td><select name="mode"><option value="general">General</option><option value="print">Print</option><option value="summary">Summary</option></select></td></tr>
				
				<tr><td align="center"><input type="submit" value="Display" align="absmiddle" /></td></tr>
				<input type="hidden" name="task" value="display" />
				</form>
				</table>
			
		
<?php
		}//END OF TASK=DISPLAY FORM
}//END OF ISSET GET

else if(isset($_POST['task']))
{
?>

		| <a href="display.php">Display </a> | <a href=pbook_display.php?task=displayform>Purchase Book</a></div>
		<h3>Purchase Book <?php if($_POST['mode']=='summary') echo" summary " ?>From <font color="#FF6600"><?php echo $_POST['fromdate']; ?></font> To <font color="#FF6600"><?php echo $_POST['todate']; ?></font></h3>

<?php		

		if(!$link=mysql_connect("localhost",$user,$pass))
				die("Error in Database Connection");

		if(!mysql_select_db("bs"))
				echo("Error in Database Selection");
		

		if($_POST['task']=="display")
		{
				$valid=1;
				
				//VALIDATING DATEs
				
				if(!ereg("^[0-3]?[0-9][\./\-][0-1]?[0-9][\-]20[0-9][0-9]$",$_POST['fromdate'])) 
				{
						echo "Check Your Input 'From' Date<br>";
						$valid=0;
				}
				else
				{
					$datearr=explode("-",$_POST['fromdate']);
					if(!checkdate($datearr[1],$datearr[0],$datearr[2]))
					{
							echo "Check Your Input 'From' Date<br>";
							$valid=0;
					}
					else
							$fromdate=date("Y-m-d",mktime(0,0,0,$datearr[1],$datearr[0],$datearr[2]));
				}
				
				
				if(!ereg("^[0-3]?[0-9][\./\-][0-1]?[0-9][\-]20[0-9][0-9]$",$_POST['todate'])) 
				{
						echo "Check Your Input 'To' Date<br>";
						$valid=0;
				}
				else
				{
					$datearr=explode("-",$_POST['todate']);
					if(!checkdate($datearr[1],$datearr[0],$datearr[2]))
					{
							echo "Check Your Input 'To' Date<br>";
							$valid=0;
					}
					else
							$todate=date("Y-m-d",mktime(0,0,0,$datearr[1],$datearr[0],$datearr[2]));
				}
				
				
				
			
				if(isset($_POST['ledger']) and $_POST['ledger']!='ALL')
					$ledger=" and p.ledger_id=".$_POST['ledger'];
				else
					$ledger="";
			
				if(isset($_POST['pledger']) and $_POST['pledger']!='ALL')
					$pledger=" and p.purchase_ledger_id=".$_POST['pledger'];
				else
					$pledger="";
				
				if(isset($_POST['search']) and !empty($_POST['search']) )
					$search=" and bill_no LIKE '".$_POST['search']."'";
				else
					$search="";

				
				//FILTER FORM	(Not provided in summary mode)
				if($_POST['mode']!='summary')
				{

?>
		
				<form name="filterform" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post" id='remove_for_print_form'>
				<table class="main" cellpadding="3">
				<tr>
				<td><select name="ledger">
					<option value="ALL">All Party Ledgers for</option>
<?php					
				//EXTRACT ALL SUNDRY CREDITORS AND DEBTORS
				$result=mysql_query("select * from bs_groups where gpname='Sundry Debtors'");
				if(!$result)
						echo("Error in Query Execution");
				$arr=mysql_fetch_array($result);
				extract_ledger_ids_under($arr['id']);
				//BANK ACCOUNT
				$result=mysql_query("select * from bs_groups where gpname='Sundry Creditors'");
				if(!$result)
						echo("Error in Query Execution");
				$arr=mysql_fetch_array($result);
				extract_ledger_ids_under($arr['id']);
?>				
				</select></td>
				<td><select name="pledger">
				<option value="ALL">All Purchase Accounts</option>
<?php					
				//EXTRACT ALL PURCHASE ACCOUNT
				$result=mysql_query("select * from bs_groups where gpname='Purchase Account'");
				if(!$result)
						echo("Error in Query Execution");
				$arr=mysql_fetch_array($result);
				extract_ledger_ids_under($arr['id']);
?>				
				</select>
				</td>
				<td><select name="detail">
					<option value="no">Without Detail</option>
					<option value="yes">With Detail</option>
					</select>
				</td>
				<td><input type="submit" value="Go" /></td>
				<input type="hidden" name="task" value="display" />
				<input type="hidden" name="fromdate" value="<?php echo $_POST['fromdate'] ?>" />				
				<input type="hidden" name="todate" value="<?php echo $_POST['todate'] ?>" />	
				<input type="hidden" name="search" value="<?php echo $_POST['search'] ?>" />					
				<input type="hidden" name="mode" value="<?php echo $_POST['mode'] ?>" />					
							
				</tr>
				</table>
				</form>
<?php				
				} //end of if mode!='summary'
				if($valid==1)
				{
					if($_POST['mode']=='summary')
					{
									$result=mysql_query("select bl.ldname as ldname, sum(pb.amount) as amount from bs_purchase_book pb,bs_ledgers bl where pb.ledger_id=bl.id and pb.date>='{$fromdate}' and pb.date<='{$todate}' group by pb.ledger_id order by ldname");
								

									if(!$result)
										echo "Purchase Book Extraction Err: ".mysql_error();
  								else
	  							{
										
												//DISPLAY THE SUMMARY TABLE
												echo "<table border='1' class='display_panel' cellpadding='1' >";
												echo "<tr><th>S No</th><th>Party Name</th><th>Total Purchase</th><tr>";
										
    										$row=1;
    										while($arr=mysql_fetch_array($result))
							    			{
											
														echo "<tr "; if($row%2==0)echo"bgcolor='#99CCFF'";else echo"bgcolor='#99FFFF'";  echo"><td>{$row}</td><td>{$arr['ldname']}</td><td><div align='right'><b>{$arr['amount']}</b></div></td></tr>";
 														$row++;
								
	     									}//END OF $ARR ITEERATION
						
									}
									
										//EXTRACTING Sales SUM
										$result1=mysql_query("select sum(amount) as total from  (select bl.ldname as ldname, sum(pb.amount) as amount from bs_purchase_book pb,bs_ledgers bl where pb.ledger_id=bl.id and pb.date>='{$fromdate}' and pb.date<='{$todate}' group by pb.ledger_id ) as temp");
										if(!$result1)
												echo "Purchase Book Extraction Err: ".mysql_error();
										else
												$arr1=mysql_fetch_array($result1);
												
										echo "<tr "; if($row%2==0)echo"bgcolor='#99CCFF'";else echo"bgcolor='#99FFFF'";  echo"><td></td> <td><b>Total</b></td>  <td><div align='right'><b>"."Rs "."{$arr1['total']}</b></div></td></tr>";

										
										echo "</table>";
					
					}//end of mode =='summary'
					else
					{
				
						if(isset($_POST['detail']) && $_POST['detail']=="yes")
						{
							if($_POST['mode']=='general')
							{
								$result=mysql_query("select *,p.id as pid,l1.ldname as ldname,l2.ldname as pldname from bs_purchase_book as p,bs_ledgers as l1,bs_ledgers as l2 where p.ledger_id=l1.id and p.purchase_ledger_id=l2.id and p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search );
							}
							else if($_POST['mode']=='print')
							{
								$result=mysql_query("select *,p.id as pid,l1.ldname as ldname,l2.ldname as pldname from bs_purchase_book as p,bs_ledgers as l1,bs_ledgers as l2 where p.ledger_id=l1.id and p.purchase_ledger_id=l2.id and p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search."order by p.date" );
							}
							
								if(!$result)
										echo "Purchase Book Extraction Err: ".mysql_error();
								else
								{
										
												echo "<table border='1' class='display_panel' cellpadding='1' >";
												echo "<tr>";if($_POST['mode']=='print')echo"<th>S No</th>";else echo"<th>Purchase Id</th>";echo"<th>Date</th><th>Bill No</th><th>Party Name</th><th>Bale No</th><th>LR No</th><th>Description</th><th>Quantity</th><th>Rates</th><th>Less</th><th>Add</th><th>Nett Amount</th><th>Purchase Ledger</th><tr>";
										$row = 1;
										while($arr=mysql_fetch_array($result))
										{
												//DISPLAY THE BILL TABLE
												//DATE CONVERSION TO DD-MM-YYYY
												$arrdate=explode("-",$arr['date']);
												$date=date("d-m-Y",mktime(0,0,0,$arrdate[1],$arrdate[2],$arrdate[0]));								
												
										
												echo "<tr>";if($_POST['mode']=='print')echo"<td>{$row}</td>";else echo"<td><a href='pbook_trans.php?task=edit_form&pid={$arr['pid']}'>{$arr['pid']}</a></td>";echo"<td>{$date}</td><td>{$arr['bill_no']}</td><td>{$arr['ldname']}</td><td>{$arr['bale_no']}</td><td>{$arr['lr_no']}</td><td>{$arr['description']}</td><td>{$arr['quantity']}</td><td>{$arr['rate']}</td><td>{$arr['less']}</td><td>{$arr['add']}</td><td><div align='right'><b>{$arr['amount']}</b></div></td><td>{$arr['pldname']}</td></tr>";
												
						
												
												$row++;												
										}//END OF $ARR ITEERATION
												echo "</table>";										
												echo"<hr>";
										
										
										//EXTRACTING PURCHASE SUM
										$result1=mysql_query("select sum(amount) as total from bs_purchase_book as p where  p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search);
										if(!$result1)
												echo "Purchase Book Extraction Err: ".mysql_error();
										else
												$arr1=mysql_fetch_array($result1);
										
										echo "<table border='1' class='sub' cellpadding='1'>";		
										echo "<tr><td></td>  <td></td>  <td></td>  <td><b>Total</b></td>  <td></td>  <td></td>  <td></td>  <td></td>  <td></td>  <td></td>  <td><div align='right'><b>{$arr1['total']} Rs</b></div></td>  <td></td>  </tr>";

										
										echo "</table>";
								}//END OF VALID IF
							
						
						}//END OF DETAIL
						else
						{
						
								if($_POST['mode']=='general')
								{
									$result=mysql_query("select *,p.id as pid,l1.ldname as ldname,l2.ldname as pldname from bs_purchase_book as p,bs_ledgers as l1,bs_ledgers as l2 where p.ledger_id=l1.id and p.purchase_ledger_id=l2.id and p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search );
								}
								else if($_POST['mode']=='print')
								{
									$result=mysql_query("select *,p.id as pid,l1.ldname as ldname,l2.ldname as pldname from bs_purchase_book as p,bs_ledgers as l1,bs_ledgers as l2 where p.ledger_id=l1.id and p.purchase_ledger_id=l2.id and p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search." order by p.date" );
								
								}
								
								if(!$result)
										echo "Purchase Book Extraction Err: ".mysql_error();
								else
								{
										
										echo "<table border='1' class='display_panel' cellpadding='1' >";
										echo "<tr>";if($_POST['mode']=='print')echo"<th>S No</th>";else echo"<th>Purchase Id</th>";echo"<th>Date</th><th>Bill No</th><th>Party Name</th><th>Nett Amount</th><th>Purchase Ledger</th><tr>";										
										$row=1;
										while($arr=mysql_fetch_array($result))
										{
					
												//DATE CONVERSION TO DD-MM-YYYY
												$arrdate=explode("-",$arr['date']);
												$date=date("d-m-Y",mktime(0,0,0,$arrdate[1],$arrdate[2],$arrdate[0]));								
												
												//DISPLAYING
												
														echo "<tr "; if($row%2==0)echo"bgcolor='#99CCFF'";else echo"bgcolor='#99FFFF'";  echo">";if($_POST['mode']=='print')echo"<td>{$row}</td>";else echo"<td><a href='pbook_trans.php?task=edit_form&pid={$arr['pid']}'>{$arr['pid']}</a></td>";echo"<td>{$date}</td><td >{$arr['bill_no']}</td><td>{$arr['ldname']}</td><td><div align='right'><b>{$arr['amount']}</b></div></td><td>{$arr['pldname']}</td></tr>";
												
												$row++;
										}
										
										//EXTRACTING PURCHASE SUM
										$result1=mysql_query("select sum(amount) as total from bs_purchase_book as p where  p.date>='{$fromdate}' and p.date<='{$todate}' ".$ledger.$pledger.$search);
										if(!$result1)
												echo "Purchase Book Extraction Err: ".mysql_error();
										else
												$arr1=mysql_fetch_array($result1);
												
										echo "<tr "; if($row%2==0)echo"bgcolor='#99CCFF'";else echo"bgcolor='#99FFFF'";  echo"><td></td>  <td></td>  <td></td>  <td><b>Total</b></td>  <td><div align='right'><b>{$arr1['total']}</b></div></td>  <td>Rs</td>  </tr>";

										
										echo "</table>";
								}
								
						}//END OF WITHOUT DETAIL
											
          }//END of else part => mode !=summary
				
				
				}//END OF IF VALID=1;

					
		}//END OF TASK=DISPLAY
}//END OF ISSET POST		

?>
